﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace 毕业设计__排课软件
{
    /// <summary>
    /// Confirm.xaml 的交互逻辑
    /// </summary>
    public partial class Confirm : Window
    {
        public List<Array> ClassList { get; }
        public TeachingProject Kecheng { get; }
        public string Jiaoshi { get; }
        public string Shijian { get; }

        public Confirm(List<Array> classList, TeachingProject kecheng, string jiaoshi, string shijian)
        {
            InitializeComponent();
            string banji = "";
            foreach (Array aa in classList)
            {

                banji = banji + aa.GetValue(0);
                banji = banji + aa.GetValue(1);
                banji = banji + aa.GetValue(2);

            }

            TbPaikeInfo.Text = "班级信息：" + banji + "\n" +
                               "课程名称：" + kecheng.CourseName + "\n" +
                               "教室信息：" + jiaoshi + "\n" +
                               "时间信息：周" + Convert.ToInt32(shijian) / 10 + "第" + Convert.ToInt32(shijian) % 10 + "节";
            ClassList = classList;
            Kecheng = kecheng;
            Jiaoshi = jiaoshi;
            Shijian = shijian;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {

        }

        private void Button_OnClick(object sender, RoutedEventArgs e)
        {
            //先查询教师冲突
            string _dbPath;
            var path0 = Environment.CurrentDirectory;
            var path1 = path0.Substring(0, path0.LastIndexOf("\\", StringComparison.Ordinal));
            var path2 = path1.Substring(0, path1.LastIndexOf("\\", StringComparison.Ordinal));
            _dbPath = path2 + "\\DB\\bs_pk.db";                                                  //sqlite数据库文件目录获取

            //查询是否冲突

            string teacherId = null;
            string CourseId = null;
            List<string> ClassID = new List<string>();
            string ClassroomId = null;
            #region 数据库操作  查询教师ID，课程ID，教室ID，班级ID
            using (var conn = new SQLiteConnection())
            {
                var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                conn.ConnectionString = connsb.ToString();
                conn.Open();

                try
                {
                    using (new SQLiteCommand(conn))//查询教师ID,课程ID
                    {
                        string sql = "select Teacher_ID,Lesson_Info.Course_ID from Lesson_Info,Lesson_Teacher where Lesson_Info.Course_ID = Lesson_Teacher.Course_ID and Lesson_Info.Course_ID = '" + Kecheng.CourseId + "'";
                        var command = new SQLiteCommand(sql, conn);
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            teacherId = reader["Teacher_ID"].ToString();
                            CourseId = reader["Course_ID"].ToString();
                        }
                    }

                    using (new SQLiteCommand(conn))//查询教室ID
                    {
                        string sql = "select Classroom_ID from Classroom_Info WHERE Classroom_Name = '" + Jiaoshi + "'";
                        var command = new SQLiteCommand(sql, conn);
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            ClassroomId = reader["Classroom_ID"].ToString();
                        }
                    }

                    using (new SQLiteCommand(conn))//查询班级ID
                    {
                        foreach (var i in ClassList)
                        {
                            string sql = "SELECT Class_ID FROM Class_Info where Grade = '" + i.GetValue(0) + "' and major = '" + i.GetValue(1) + "' and Class = '" + i.GetValue(2) + "'";
                            var command = new SQLiteCommand(sql, conn);
                            var reader = command.ExecuteReader();
                            while (reader.Read())
                            {
                                ClassID.Add(reader["Class_ID"].ToString());
                            }
                        }

                    }

                }
                catch (Exception)
                {
                    // ignored 
                }
            }
            #endregion
            //获取要排布的周数
            int a = 0;
            int b = 0;
            try
            {
                a = Convert.ToInt32(textBox1.Text);
                b = Convert.ToInt32(textBox2.Text);

                if (a>b)
                {
                    MessageBox.Show("输入的不对，咋能从小到大输入呢？");
                    return;
                }
            }
            catch (Exception )
            {
                MessageBox.Show("输入的不对，输入数字，你输入啥了？");
                return;
            }
            
            string teachingWeek;
            //Dictionary<string,List<string>> chongtuDictionary = new Dictionary<string, List<string>>();
            //List<string> aa = new List<string>();
            //chongtuDictionary.Add("教师冲突", aa);
            //chongtuDictionary.Add("教室冲突", aa);
            //chongtuDictionary.Add("班级冲突", aa);
            List<string> teacherchongtu = new List<string>();
            List<string> classroomchongtu = new List<string>();
            List<string> classchongtu = new List<string>();


            bool chongtuTest = false;
            //MessageBox.Show(teacherId);
            string chongtuMessage = "";
            for (int i = a; i <= b; i++)  //时间统一格式为四位，前两位是教学周，后两位是课程的星期和第几节课，如果出现排第一周的情况将‘1’==>‘01’
            {
                teachingWeek = i.ToString();
                if (i < 10)
                {
                    teachingWeek = '0' + teachingWeek;
                }

                #region 数据库操作  查询教师的冲突
                using (var conn = new SQLiteConnection())
                {
                    var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                    conn.ConnectionString = connsb.ToString();
                    conn.Open();

                    try
                    {
                        using (new SQLiteCommand(conn))
                        {
                            string sql = "select *from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Lesson_Teacher where Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Lesson_Info.Course_ID=Lesson_Teacher.Course_ID and Lesson_Teacher.Teacher_ID= Teacher_Info.Teacher_ID and Teaching_Time = '" + teachingWeek + Shijian + "' and Teacher_Info.Teacher_ID = " + teacherId + "";
                            var command = new SQLiteCommand(sql, conn);
                            var reader = command.ExecuteReader();
                            
                            string str = null;
                            List<string> strList = new List<string>();
                            while (reader.Read())
                            {
                                chongtuTest = true;
                                str = reader["Teacher_Name"] + "在" + Convert.ToInt32(reader["Teaching_Time"]) / 100 + "周，星期" + (Convert.ToInt32(reader["Teaching_Time"]) % 100) / 10 + "第" + Convert.ToInt32(reader["Teaching_Time"]) % 10 + "节课已经排课了";
                                strList.Add(str);
                                
                            }
                            if (strList.Count > 0)
                            {
                                //chongtuList.Add();
                                //chongtuDictionary.Add("教师冲突", strList);
                                //chongtuDictionary["教师冲突"] = strList;
                                foreach (var item2 in strList)
                                {
                                    teacherchongtu.Add(item2);
                                    //List<string> tes = new List<string>();
                                    //tes = chongtuDictionary["教师冲突"];
                                    //tes.Add(item2);
                                    //chongtuDictionary["教师冲突"] = tes;
                                }
                            }
                        }
                    }
                    catch (Exception)
                    {
                        // ignored 
                    }
                }
                #endregion

                #region 数据库操作  查询教室冲突
                using (var conn = new SQLiteConnection())
                {
                    var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                    conn.ConnectionString = connsb.ToString();
                    conn.Open();

                    try
                    {
                        using (new SQLiteCommand(conn))
                        {
                            string sql = "select *from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Lesson_Teacher where Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Lesson_Info.Course_ID=Lesson_Teacher.Course_ID and Lesson_Teacher.Teacher_ID= Teacher_Info.Teacher_ID and Teaching_Time = '" + teachingWeek + Shijian + "' and Classroom_Name = '" + Jiaoshi + "'";
                            var command = new SQLiteCommand(sql, conn);
                            var reader = command.ExecuteReader();
                            List<string> strList = new List<string>();
                            string str = null;
                            while (reader.Read())
                            {
                                chongtuTest = true;
                                str = reader["Classroom_Name"] + "在" + Convert.ToInt32(reader["Teaching_Time"]) / 100 + "周，星期" + (Convert.ToInt32(reader["Teaching_Time"]) % 100) / 10 + "第" + Convert.ToInt32(reader["Teaching_Time"]) % 10 + "节课已经排课了";
                                strList.Add(str);
                                //teacherId = reader["Teacher_ID"].ToString();//将年级专业班级信息拼接成一个完整班级信息显示在前台listbox当中
                            }
                            if (strList.Count > 0)
                            {
                                //chongtuDictionary.Add("教室冲突", strList);
                                //chongtuDictionary["教室冲突"] = strList;
                                foreach (var item2 in strList)
                                {
                                    classroomchongtu.Add(item2);
                                    //chongtuDictionary["教室冲突"].Add(item2);
                                }
                            }
                        }
                    }
                    catch (Exception)
                    {
                        // ignored 
                    }
                }

                #endregion

                #region 数据库操作  查询班级冲突

                foreach (var item in ClassID)
                {
                    using (var conn = new SQLiteConnection())
                    {
                        var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                        conn.ConnectionString = connsb.ToString();
                        conn.Open();

                        try
                        {
                            using (new SQLiteCommand(conn))
                            {
                                string sql = "select *from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Lesson_Teacher, Class_Info where Class_Info.Class_ID = Configuration_Info.Class_ID and Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Lesson_Info.Course_ID=Lesson_Teacher.Course_ID and Lesson_Teacher.Teacher_ID= Teacher_Info.Teacher_ID and Teaching_Time = '" + teachingWeek + Shijian + "' and Configuration_Info.Class_ID = '" + item + "'";
                                var command = new SQLiteCommand(sql, conn);
                                var reader = command.ExecuteReader();

                                string str = null;
                                List<string> strList = new List<string>();

                                while (reader.Read())
                                {
                                    chongtuTest = true;
                                    str = reader["Grade"].ToString() + reader["major"]+reader["Class"] + "在" + Convert.ToInt32(reader["Teaching_Time"]) / 100 + "周，星期" + (Convert.ToInt32(reader["Teaching_Time"]) % 100) / 10 + "第" + Convert.ToInt32(reader["Teaching_Time"]) % 10 + "节课已经排课了";
                                    strList.Add(str);
                                    //teacherId = reader["Teacher_ID"].ToString();//将年级专业班级信息拼接成一个完整班级信息显示在前台listbox当中
                                }
                                if (strList.Count > 0)
                                {
                                    //chongtuDictionary.Add("班级冲突", strList);
                                    //chongtuDictionary["班级冲突"] = strList;
                                    foreach (var item2 in strList)
                                    {
                                        classchongtu.Add(item2);
                                        //chongtuDictionary["班级冲突"].Add(item2);
                                    }
                                }
                            }
                        }
                        catch (Exception)
                        {
                            // ignored 
                        }
                    }
                }

                #endregion
                

            }

            if (chongtuTest)
            {
                foreach (var item in teacherchongtu)
                {
                    chongtuMessage = chongtuMessage + "教师冲突：" + item + "\n";
                }
                foreach (var item in classroomchongtu)
                {
                    chongtuMessage = chongtuMessage + "教室冲突：" + item + "\n";
                }
                foreach (var item in classchongtu)
                {
                    chongtuMessage = chongtuMessage + "班级冲突：" + item + "\n";
                }
                //foreach (var item in chongtuDictionary)
                //{
                //    foreach (var item1 in item.Value)
                //    {
                //        chongtuMessage = chongtuMessage + item.Key + item1 + "\n";
                //        //MessageBox.Show(item.Key + item1 + "\n");
                //    }

                //}
                ChongTuInfo chongtuMG = new ChongTuInfo(chongtuMessage);
                chongtuMG.Show();
                return;
            }

            for (int i = a; i <= b; i++) //时间统一格式为四位，前两位是教学周，后两位是课程的星期和第几节课，如果出现排第一周的情况将‘1’==>‘01’
            {
                teachingWeek = i.ToString();
                if (i < 10)
                {
                    teachingWeek = '0' + teachingWeek;
                }

                #region 数据库操作  插入排课信息

                foreach (var CI in ClassID)
                {
                    using (var conn = new SQLiteConnection())
                    {
                        var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                        conn.ConnectionString = connsb.ToString();
                        conn.Open();

                        try
                        {
                            using (new SQLiteCommand(conn))
                            {
                                string sql = "INSERT INTO Configuration_Info(Class_ID,Course_ID,Teacher_ID,Classroom_ID,Teaching_Time) VALUES(" + CI + "," + CourseId + "," + teacherId + "," + ClassroomId + ",'" + teachingWeek + Shijian + "')";
                                var command = new SQLiteCommand(sql, conn);
                                var reader = command.ExecuteReader();

                                while (reader.Read())
                                {
                                    MessageBox.Show(reader.ToString());

                                }
                            }
                        }
                        catch (Exception)
                        {
                            // ignored 
                        }
                    }
                }


                #endregion
            }
            MessageBox.Show("排课成功！");
            Close();
            //不冲突存入数据库

            //显示成功
        }

        private void Button_OnClick_Close(object sender, RoutedEventArgs e)
        {
            Close();
        }
    }
}
